﻿Imports Microsoft.VisualBasic
Imports System.Data.SqlClient

Public Class KhuyenMaiTheoHotelServer

    Public Shared Function GetListKhuyenMaiByIDHotel(ByVal id As Guid) As List(Of KhuyenMaiTheoHotel)
        Dim listKhuyenMai As New List(Of KhuyenMaiTheoHotel)
        Try
         
            Dim sql As String = "SELECT a.ID, a.NAME,d.NAME AS TEN_KS, a.MEMBERSHIP,a.GIAM_GIA, CAST(a.NOI_DUNG as nvarchar(max)) as NOI_DUNG, "
            sql &= " a.GIA, a.GIA_GOC, a.DANH_MUC_ID,  CAST(a.PHU_THU as nvarchar(max)) as PHU_THU, "
            sql &= " CAST(a.LUU_Y as nvarchar(max)) as LUU_Y,  a.HIEN_THI_GIA, a.DISPLAY,  a.STT,"
            sql &= " a.NGAY_BAT_DAU, a.NGAY_KET_THUC,  MIN(b.GIA) AS GIA_PHONG_RE, isNull(a.GIA_KHACH_SAN,0) GIA_KHACH_SAN, c.ID as IDLOAIPHONG, "
            sql &= " CASE WHEN a.GIA < MIN(b.GIA) THEN a.GIA ELSE MIN(b.GIA) END AS GIA_RE"
            sql &= " FROM KHUYEN_MAI a,KHUYEN_MAI_LOAI_PHONG b, LOAI_PHONG c,KHACH_SAN d"
            sql = String.Format(" {0} WHERE a.KHACH_SAN_ID ='{1}' ", sql, id)
            sql &= " AND a.KHACH_SAN_ID = d.ID AND d.DELETED = '0' AND d.DISPLAY = '1'"
            sql &= " AND( (a.NGAY_BAT_DAU <= GETDATE() AND a.NGAY_KET_THUC >= GETDATE())"
            sql &= " OR a.NGAY_BAT_DAU >= GETDATE()) AND a.DELETED = '0' AND a.DISPLAY = '1'"
            sql &= " AND b.ID_KHUYEN_MAI = a.ID AND b.ID_LOAI_PHONG = c.ID"
            sql &= " AND b.DELETED = '0' AND c.DELETED = '0'AND c.DISPLAY = '1'"
            sql &= " GROUP BY a.ID, a.NAME,  d.NAME,  a.MEMBERSHIP,  a.GIAM_GIA,"
            sql &= " CAST(a.NOI_DUNG as nvarchar(max)),  a.GIA,  a.GIA_GOC, "
            sql &= " a.DANH_MUC_ID, CAST(a.PHU_THU as nvarchar(max)), CAST(a.LUU_Y as nvarchar(max)),  "
            sql &= " a.HIEN_THI_GIA, a.DISPLAY,"
            sql &= " a.STT, a.NGAY_BAT_DAU, a.NGAY_KET_THUC, a.GIA_KHACH_SAN, c.ID"
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            Dim idKM As New Guid
            While (reader.Read())
                If idKM <> reader("ID") Then
                    idKM = reader("ID")
                    Dim khuyenMai As New KhuyenMaiTheoHotel
                    khuyenMai.GIA_RE = DirectCast(reader("GIA"), Decimal)
                    If Not reader.IsDBNull(reader.GetOrdinal("NAME")) Then
                        khuyenMai.NAME_KM = reader("NAME")
                    End If
                    khuyenMai.HIENTHIGIA = reader("HIEN_THI_GIA")
                    khuyenMai.NOI_DUNG = reader("NOI_DUNG")
                    khuyenMai.IDKHUYENMAI = reader("ID")
                    khuyenMai.GIA_GOC = DirectCast(reader("GIA_GOC"), Decimal)
                    khuyenMai.PHU_THU = reader("PHU_THU")
                    khuyenMai.LUU_Y = reader("LUU_Y")
                    khuyenMai.NGAY_BAT_DAU = reader("NGAY_BAT_DAU")
                    khuyenMai.NGAY_KET_THUC = reader("NGAY_KET_THUC")
                    khuyenMai.GIA_KHACH_SAN = DirectCast(reader("GIA_KHACH_SAN"), Decimal)
                    khuyenMai.IDLOAIPHONG = reader("IDLOAIPHONG")
                    listKhuyenMai.Add(khuyenMai)
                End If          
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try
        Return listKhuyenMai
    End Function

    Public Shared Function GetKhuyenMaiByIDKhuyenMai(ByVal idKS As Guid, ByVal idKM As Guid) As KhuyenMaiTheoHotel
        Dim khuyenMai As New KhuyenMaiTheoHotel
        Try

            Dim sql As String = "SELECT a.ID, a.NAME,d.NAME AS TEN_KS, a.MEMBERSHIP,a.GIAM_GIA, CAST(a.NOI_DUNG as nvarchar(max)) as NOI_DUNG, "
            sql &= " a.GIA, a.GIA_GOC, a.DANH_MUC_ID,  CAST(a.PHU_THU as nvarchar(max)) as PHU_THU, "
            sql &= " CAST(a.LUU_Y as nvarchar(max)) as LUU_Y,  a.HIEN_THI_GIA, a.DISPLAY,  a.STT,"
            sql &= " a.NGAY_BAT_DAU, a.NGAY_KET_THUC,  MIN(b.GIA) AS GIA_PHONG_RE, isNull(a.GIA_KHACH_SAN,0) GIA_KHACH_SAN, c.ID as IDLOAIPHONG, "
            sql &= " CASE WHEN a.GIA < MIN(b.GIA) THEN a.GIA ELSE MIN(b.GIA) END AS GIA_RE"
            sql &= " FROM KHUYEN_MAI a,KHUYEN_MAI_LOAI_PHONG b, LOAI_PHONG c,KHACH_SAN d"
            sql = String.Format(" {0} WHERE a.KHACH_SAN_ID ='{1}' and a.ID='{2}'", sql, idKS, idKM)
            sql &= " AND a.KHACH_SAN_ID = d.ID AND d.DELETED = '0' AND d.DISPLAY = '1'"
            sql &= " AND( (a.NGAY_BAT_DAU <= GETDATE() AND a.NGAY_KET_THUC >= GETDATE())"
            sql &= " OR a.NGAY_BAT_DAU >= GETDATE()) AND a.DELETED = '0' AND a.DISPLAY = '1'"
            sql &= " AND b.ID_KHUYEN_MAI = a.ID AND b.ID_LOAI_PHONG = c.ID"
            sql &= " AND b.DELETED = '0' AND c.DELETED = '0'AND c.DISPLAY = '1'"
            sql &= " GROUP BY a.ID, a.NAME,  d.NAME,  a.MEMBERSHIP,  a.GIAM_GIA,"
            sql &= " CAST(a.NOI_DUNG as nvarchar(max)),  a.GIA,  a.GIA_GOC, "
            sql &= " a.DANH_MUC_ID, CAST(a.PHU_THU as nvarchar(max)), CAST(a.LUU_Y as nvarchar(max)),  "
            sql &= " a.HIEN_THI_GIA, a.DISPLAY,"
            sql &= " a.STT, a.NGAY_BAT_DAU, a.NGAY_KET_THUC, a.GIA_KHACH_SAN, c.ID"
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())

                khuyenMai.GIA_RE = DirectCast(reader("GIA"), Decimal)
                If Not reader.IsDBNull(reader.GetOrdinal("NAME")) Then
                    khuyenMai.NAME_KM = reader("NAME")
                End If
                khuyenMai.HIENTHIGIA = reader("HIEN_THI_GIA")
                khuyenMai.NOI_DUNG = reader("NOI_DUNG")
                khuyenMai.IDKHUYENMAI = reader("ID")
                khuyenMai.GIA_GOC = DirectCast(reader("GIA_GOC"), Decimal)
                khuyenMai.PHU_THU = reader("PHU_THU")
                khuyenMai.LUU_Y = reader("LUU_Y")
                khuyenMai.NGAY_BAT_DAU = reader("NGAY_BAT_DAU")
                khuyenMai.NGAY_KET_THUC = reader("NGAY_KET_THUC")
                khuyenMai.GIA_KHACH_SAN = DirectCast(reader("GIA_KHACH_SAN"), Decimal)
                khuyenMai.IDLOAIPHONG = reader("IDLOAIPHONG")
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try
        Return khuyenMai
    End Function

End Class
